Skip to main content

Creating a Fly.io Database

For this example we will use tables that can be used with the todo tutorial.

Create an Account​

First you need to create an account and add a credit card for free allowances, you can sign up here

For new fly.io customers

Taken directly from fly.io website "When you sign up for a Fly.io account, we create a default (“personal”) organization for you, which receives a one-time $5 sign-up credit to let you test-drive Fly.io at no cost. This organization’s $5/month Hobby plan subscription does not start until the credit has been used up. Usage that falls outside of our free resource allowances consumes your sign-up credit.

Once this credit is exhausted, the organization is automatically migrated to the $5/month Hobby plan (we’ll send you an email when it happens). There’s no time limit to use up the sign-up credit and move to the paid subscription."

Install flyctl and Authenticate Account​

MacOS​

If you have the Homebrew package manager installed, flyctl can be installed by running:

brew install flyctl

Alternatively, use the install script:

curl -L https://fly.io/install.sh | sh

If you used curl to install flyctl, then you need to add the flyctl directory to your shell rc file. Check the output of the install script for the entries to copy and paste into the file. Now you can use the flyctl command from any directory. Or for maximum efficiency, you can use the fly command!

Linux​

Run the install script:

curl -L https://fly.io/install.sh | sh

Windows​

Run the PowerShell install script:

pwsh -Command "iwr https://fly.io/install.ps1 -useb | iex"

If you encounter an error saying the pwsh command is not found, PowerShell can be used instead, though we recommend installing the latest version of PowerShell.

info

Whichever system you install flyctl in follow the instructions in the terminal after it has been installed.

Sign In

Authenticate your account by running:

$ fly auth login

I installed Linux and had a bit of trouble with this step, I needed to run:

/home/<foldername>/.fly/bin/flyctl auth login

Microsoft WSL users may need to run:

ln -s /usr/bin/wslview /usr/local/bin/xdg-open

Whichever route you take you will be returned to your command line, ready to use Fly.io!

Create PostgreSQL​

Run the command to create a postgres cluster: 

flyctl postgres create

Answer the following prompts: 

    ? Choose an app name (leave blank to generate one): < Devii requires letters, numbers, and underscores only>
    ? Select Organization: <This should be what you used to sign up with>
    ? Select regions: <whatever region is closest to you>
    ? Select configuration: Development - Single node, 1x shared CPU, 256MB RAM, 1GB disk
note

By selecting the “Development” single-node cluster configuration, then you can choose to scale down to zero if there are no open connections after one hour.

danger

Upon database creation, save the credentials provided in the message.

Next, enter the command: 

$flyctl postgres connect -a <db_name>

Create Database​

The command line change to postgres=# from here we can enter PostgreSQL commands such as creating a database, creating tables, inserting values, etc. The first value you may want to enter is \l to show you all the databases that you have, you will notice that the first value is postgres, this is the actual name of a database. To create a database with your preferred name you need to enter the command at the postgres=#: 

CREATE DATABASE <db_name>;

It should show “CREATE DATABASE” as the next line, if you enter \l again you will now see the name of the database you just created at the top of the table.

Next, you will need to quit the database that you are currently in by using the command \q.

Then you will need to connect to the database you just created. 

$ flyctl postgres connect -d <db_name>

The command line will now change to <db_name>=#, from here we can add tables and data to our new database. The following SQL code and resultant tables can be used in the ToDo app tutorial. The first table will be a list table that will contain the names and id of lists that items will belong to and can be sorted by listid.

Create Tables​

CREATE TABLE List (
ListID SERIAL NOT NULL PRIMARY KEY, 
ListName varchar(255) NOT NULL
);

Second table will be a item table that will relate to the list table:

CREATE TABLE Item (
ItemID SERIAL NOT NULL PRIMARY KEY,
ItemName varchar(255) NOT NULL,
ListID INT,
CONSTRAINT FK_ListID
  FOREIGN KEY (ListID)
  REFERENCES List(ListID)
  ON DELETE CASCADE
);

Insert Values​

Next we will insert values into the tables we just created which will return the values we just created. 

INSERT INTO list(listname) VALUES('tutorial') RETURNING *;
INSERT INTO item(itemname, listid) VALUES('Create Tutorial', 1) RETURNING *;

Once tables are created, exit the database using \q.

Configure External Connection​

You now have two tables with values, in order to connect the database with Devii we will need to create an IPV4 address for external connections.

Enter the command: 

fly ips allocate-v4 --app <db_name>

This will incur a monthly cost of $2.00, which will be deducted from your initial $5.00 from Fly.io. Now that you have an IP address, it’s time to configure your app to accept connections on an external port, and direct incoming requests to your Postgres instance.

Enter the command:

fly config save --app \<db_name\>

you will receive a response:

? An existing configuration file has been found
Overwrite file '/home/<folder_name>/fly.toml (y/N)

Type y and it may ask another question: 

? Would you like to transfer the \[build\] section from the current config to the new one? (y/N)

Type y and you will receive a response of “Wrote config file fly.toml”

Connect to Devii​

Finally we will connect Devii to your Postgres database. Navigate to your Fly.io dashboard find your database which is under “Apps” and click on it and the information for Devii connection will appear.

Your Database name the name you gave your database, the host name will be <db_name>.fly.dev, your username and password will be in the information saved in the create database step. For additional information regarding how to connect your database to Devii you can find the information HERE.

Optional: Devii Connection Verification​

To check your connection, execute the following query in the Devii portal using the GraphiQL interface:

{
  list {
    listid
    listname
    item_collection {
      itemid
      itemname
    }
  }
}

You should receive a reply of:

{
  "data": {
    "list": [
      {
        "item_collection": [
          {
            "itemid": "1",
            "itemname": " Create Tutorial"
          },
        ],
        "listid": "1",
        "listname": " Tutorial"
      }
     ]
  }
}

Learn More​

Interested in mastering SQL? We recommend the comprehensive SQL training available at W3Schools.